﻿using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.Model;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using OgilvyOne.IDAL;

namespace OgilvyOne.SqlServerDAL
{
    public class ContactUs:IContactUs
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        /// </summary>
        /// <param> 获取留言信息</param>
        /// <returns></returns>
        public IList getContactus(ContactUsModel cum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CONTACT_ID,CONTACT_NAME,CONTACT_TEL,MOBILE,CONTACT_EMAIL,ADD_TIME from COFCO_CONTACT_US ");
            if (cum.Contactname!=null)
            {
                strSql.Append(" where CONTACT_NAME='" + cum.Contactname.ToString().Trim() + "' ");
            }
            if (cum.Addtime.ToString() != "0001-1-1 0:00:00")
            {
                if (cum.Contactname != null)
                {
                    strSql.Append(" and ADD_TIME >= '" + cum.Addtime.ToString().Trim() + "'");
                }
                else
                {
                    strSql.Append(" where ADD_TIME >= '" + cum.Addtime.ToString().Trim() + "'");
                }
                
            }
            if (cum.Endtime.ToString() != "0001-1-1 0:00:00")
            {
                if (cum.Contactname != null || cum.Addtime.ToString() != "0001-1-1 0:00:00")
                {
                    strSql.Append("  and ADD_TIME <= ' " + cum.Endtime.ToString().Trim() + "'");
                }
                else
                {
                    strSql.Append(" where ADD_TIME <= ' " + cum.Endtime.ToString().Trim() + "'");
                }
            }
            strSql.Append(" order by ADD_TIME desc ");

            SqlParameter[] parms = new SqlParameter[] {
            };

            IList datalist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql.ToString(), parms))
            {
                try
                {
                    while (rdr.Read())
                    {
                        ContactUsModel cumodel = new ContactUsModel();
                        cumodel.Contactid = rdr.GetInt32(0);
                        if (!rdr.IsDBNull(1)) cumodel.Contactname = rdr.GetString(1);
                        if (!rdr.IsDBNull(2)) cumodel.Contacttel = rdr.GetString(2);
                        if (!rdr.IsDBNull(3)) cumodel.Mobile = rdr.GetString(3);
                        if (!rdr.IsDBNull(4)) cumodel.Contactemail = rdr.GetString(4);
                        if (!rdr.IsDBNull(5)) cumodel.Addtime = rdr.GetDateTime(5);

                        datalist.Add(cumodel);
                    }
                }
                catch(Exception ex)
                { }
            }
            return datalist;
        }

        /// </summary>
        /// <param> 查询一条留言</param>
        /// <returns></returns>
        private const string COLUMN_SQL = "select CONTACT_ID,CONTACT_NAME,CONTACT_TEL,MOBILE,CONTACT_EMAIL,CONTACT_TYPE,CONTACT_BUSINESS,CONTACT_PRODUCT_TYPE,CONTACT_THEME,MESSAGE,ADD_TIME from COFCO_CONTACT_US where CONTACT_ID=@CONTACT_ID";
        public ContactUsModel selectContactus(int CONTACT_ID)
        {
            ContactUsModel cum = new ContactUsModel();
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@CONTACT_ID", SqlDbType.Int)};
                parms[0].Value = CONTACT_ID;


                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, COLUMN_SQL, parms))
                {
                    if (rdr.Read())
                    {
                        cum.Contactid = rdr.GetInt32(0);
                        if (!rdr.IsDBNull(1)) cum.Contactname = rdr.GetString(1);
                        if (!rdr.IsDBNull(2)) cum.Contacttel = rdr.GetString(2);
                        if (!rdr.IsDBNull(3)) cum.Mobile = rdr.GetString(3);
                        if (!rdr.IsDBNull(4)) cum.Contactemail = rdr.GetString(4);
                        if (!rdr.IsDBNull(5)) cum.Contacttype = rdr.GetInt32(5);
                        if (!rdr.IsDBNull(6)) cum.Contactbusiness = rdr.GetString(6);
                        if (!rdr.IsDBNull(7)) cum.Contactproducttype = rdr.GetString(7);
                        if (!rdr.IsDBNull(8)) cum.Contacttheme = rdr.GetString(8);
                        if (!rdr.IsDBNull(9)) cum.Message = rdr.GetString(9);
                        if (!rdr.IsDBNull(10)) cum.Addtime = rdr.GetDateTime(10);
                    }
                }
                return cum;
        }

        /// </summary>
        /// <param> 删除单行留言</param>
        /// <returns></returns>
        public bool deleteContactu(int CONTACT_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_CONTACT_US where CONTACT_ID = @CONTACT_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@CONTACT_ID", SqlDbType.Int,4)
             };
            parameters[0].Value = CONTACT_ID;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }

        /// </summary>
        /// <param> 删除多行留言</param>
        /// <returns></returns>
        public bool DeleteContactus(string CID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_CONTACT_US where CONTACT_ID in (" + CID + ") ");
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }
    }
}
